/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model.dal;

import entity.Account;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Hailk_c00639
 */
public class AccountDAL {

    MyConnection con = new MyConnection();

    public List<Account> getAllAccount() {
        List<Account> lst = new ArrayList<>();

        try {
            CallableStatement call = con.getConnection().prepareCall("{call sp_GetAllAccount}");
            ResultSet rs = call.executeQuery();

            while (rs.next()) {
                Account acc = new Account();
                acc.setAccountID(rs.getInt("AccountID"));
                acc.setFullName(rs.getString("FullName"));
                acc.setAccountName(rs.getString("AccountName"));
                acc.setPassword(rs.getString("[Password]"));
                acc.setAge(rs.getInt("Age"));
                acc.setGender(rs.getString("Gender"));
                acc.setAddress(rs.getString("[Address]"));
                acc.setPhoneNumber(rs.getString("PhoneNumber"));
                acc.setEmail(rs.getString("Email"));
                acc.setDateOfBirth(rs.getDate("DateOfBirth"));
                acc.setCreatedDate(rs.getDate("CreatedDate"));
                acc.setRoleID(rs.getInt("RoleID"));

                lst.add(acc);
            }
        } catch (SQLException ex) {
            Logger.getLogger(AccountDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            con.closeDB();
        }
        return lst;

    }

    public Account getAccountByID(int id) {
        Account acc = null;
        
        try {
            CallableStatement call = con.getConnection().prepareCall("{call sp_GetAccountByID(?)}");
            call.setInt(1, id);
            ResultSet rs = call.executeQuery();
            if(rs.next())
            {
                acc = new Account();
                acc.setFullName(rs.getString("FullName"));
                acc.setAccountName(rs.getString("AccountName"));
                acc.setPassword(rs.getString("[Password]"));
                acc.setAge(rs.getInt("Age"));
                acc.setGender(rs.getString("Gender"));
                acc.setAddress(rs.getString("[Address]"));
                acc.setPhoneNumber(rs.getString("PhoneNumber"));
                acc.setEmail(rs.getString("Email"));
                acc.setDateOfBirth(rs.getDate("DateOfBirth"));
                acc.setCreatedDate(rs.getDate("CreatedDate"));
                acc.setRoleID(rs.getInt("RoleID"));
            }
            else
            {
                // Neu sai thi thong bao loi
            }

        } catch (SQLException ex) {
            Logger.getLogger(AccountDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            con.closeDB();
        }
        return acc;
    }

    public Account getAccountByName(String accountName) {
        Account acc = null;
        
        try {
            CallableStatement call = con.getConnection().prepareCall("{call sp_GetAccountByName(?)}");
            call.setString(1, accountName);
            ResultSet rs = call.executeQuery();
            if(rs.next())
            {
                acc = new Account();
                acc.setAccountID(rs.getInt("AccountID"));
                acc.setFullName(rs.getString("FullName"));
                acc.setPassword(rs.getString("[Password]"));
                acc.setAge(rs.getInt("Age"));
                acc.setGender(rs.getString("Gender"));
                acc.setAddress(rs.getString("[Address]"));
                acc.setPhoneNumber(rs.getString("PhoneNumber"));
                acc.setEmail(rs.getString("Email"));
                acc.setDateOfBirth(rs.getDate("DateOfBirth"));
                acc.setCreatedDate(rs.getDate("CreatedDate"));
                acc.setRoleID(rs.getInt("RoleID"));
            }
            else
            {
                // Neu sai thi thong bao loi
            }

        } catch (SQLException ex) {
            Logger.getLogger(AccountDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            con.closeDB();
        }
        return acc;

    }

    public boolean insertAccount(Account acc) {
        try {
            CallableStatement call = con.getConnection().prepareCall("{call sp_InsertAccount(?,?,?,?,?,?,?,?,?,?)}");
            call.setString(1, acc.getFullName());
            call.setString(2, acc.getAccountName());
            call.setString(3, acc.getPassword());
            call.setInt(4, acc.getAge());
            call.setString(5, acc.getGender());
            call.setString(5, acc.getAddress());
            call.setString(6, acc.getPhoneNumber());
            call.setString(7, acc.getEmail());
            call.setDate(8, acc.getDateOfBirth());
            call.setInt(9, acc.getRoleID());

            call.executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(AccountDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            con.closeDB();
        }
        return true;

    }

    public boolean updateAccount(Account acc) {
        try {
            CallableStatement call = con.getConnection().prepareCall("{call sp_UpdateAccount(?,?,?,?,?,?,?,?,?,?,?)}");
            call.setInt(1, acc.getAccountID());
            call.setString(2, acc.getFullName());
            call.setString(3, acc.getAccountName());
            call.setString(4, acc.getPassword());
            call.setInt(5, acc.getAge());
            call.setString(6, acc.getGender());
            call.setString(7, acc.getAddress());
            call.setString(8, acc.getPhoneNumber());
            call.setString(9, acc.getEmail());
            call.setDate(10, acc.getDateOfBirth());
            call.setInt(11, acc.getRoleID());

            call.executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(AccountDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            con.closeDB();
        }
        return true;

    }

    public boolean deleteAccountByID(int id) {
        try {
            CallableStatement call = con.getConnection().prepareCall("{call sp_DeleteAccountByID(?)}");
            call.setInt(1, id);
            call.execute();

        } catch (SQLException ex) {
            Logger.getLogger(AccountDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            con.closeDB();
        }
        return true;

    }

    public List<Account> searchAccountByName(String accName) {
        List<Account> lst = new ArrayList<>();

        try {
            CallableStatement call = con.getConnection().prepareCall("{call sp_SearchAccountByName(?)}");
            call.setString(1, accName);
            ResultSet rs = call.executeQuery();

            while (rs.next()) {
                Account acc = new Account();
                acc.setAccountID(rs.getInt("AccountID"));
                acc.setFullName(rs.getString("FullName"));
                acc.setAccountName(rs.getString("AccountName"));
                acc.setPassword(rs.getString("[Password]"));
                acc.setAge(rs.getInt("Age"));
                acc.setGender(rs.getString("Gender"));
                acc.setAddress(rs.getString("[Address]"));
                acc.setPhoneNumber(rs.getString("PhoneNumber"));
                acc.setEmail(rs.getString("Email"));
                acc.setDateOfBirth(rs.getDate("DateOfBirth"));
                acc.setCreatedDate(rs.getDate("CreatedDate"));
                acc.setRoleID(rs.getInt("RoleID"));

                lst.add(acc);
            }
        } catch (SQLException ex) {
            Logger.getLogger(AccountDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        finally
        {
            con.closeDB();
        }
        return lst;
    }
}
